//	Company:	HTHK
//	NAME:	    CDISearch.java
//				Category  Detail  Interior  Chargeable
//	TITLE:	    The maintence of ProjectCategory in Time Sheet System
//	FUNCTION:	The flow control interface of class ProjectCategory associate with the table
//	BY:	        Mike Cheng
//	DATE:	    15/1/2003
// --------------------------------------------------------------
// MODIFICATION HISTORY
// --------------------------------------------------------------
//	CR Log #:  99999  BY:  XXXX   DATE:  dd/mm/yyyy
//	ERROR:	XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
//	FIX:	XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
// --------------------------------------------------------------
package tss.projectreport;

import javax.servlet.*;
import javax.servlet.http.*;

import org.apache.log4j.Logger;

import java.io.*;
import java.util.*;
import java.sql.*;

import com.ffpc.sql.*;
import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

import tss.projectreport.ChargeReportContainer;
import tss.rate.*;
import tss.common.DBServlet2;

public class CDISearch extends DBServlet2 
{
	private Logger logger = Logger.getLogger(CDISearch.class);
	@Override
	protected String perform(HttpServletRequest request,
                             HttpServletResponse response,
                             StrutsMessage message)
	throws  ServletException,
            IOException,
            SQLException,
            DataException,
            Exception
	{
		HttpSession session = request.getSession();
		// IISZ updated in 2013-07-11 begin
		//Connection con = this.getConnection();
		// IISZ updated in 2013-07-11 end

		Vector vec = new Vector();

//--------------- 2004-4-22 9:21 Update By Beyes -------------------//
		String dateFrom = request.getParameter("date_from");
		String dateTo = request.getParameter("date_to");
		session.setAttribute("s_date_from1",dateFrom);
		session.setAttribute("s_date_to1",dateTo);

		String p_fromYear,p_fromMonth,p_toYear,p_toMonth;
		String p_fromDay,p_toDay;
//		p_fromYear = request.getParameter("year_from");
//		p_fromMonth = request.getParameter("month_from");
//		p_toYear = request.getParameter("year_to");
//		p_toMonth = request.getParameter("month_to");
//		session.setAttribute("year_from",p_fromYear);
//		session.setAttribute("month_from",p_fromMonth);
//		session.setAttribute("year_to",p_toYear);
//		session.setAttribute("month_to",p_toMonth);
		p_fromYear = dateFrom.substring(0,4);
		p_fromMonth = dateFrom.substring(5,7);
		p_fromDay = dateFrom.substring(8,10);
		p_toYear = dateTo.substring(0,4);
		p_toMonth = dateTo.substring(5,7);		
		p_toDay = dateTo.substring(8,10);
//-------------------------------------------------------------------//

		String companyCodeStr = "";
		if (request.getParameter("company_code") != null)
		{
			companyCodeStr = request.getParameter("company_code");
			session.setAttribute("company",companyCodeStr);
		}
		String projectCategoryStr = "";
		if (request.getParameter("cOrg_name") != null)
		{
			projectCategoryStr = request.getParameter("cOrg_name");
			session.setAttribute("project_category",projectCategoryStr);
		}
			       
/*logger.debug("company Code="+companyCodeStr);
logger.debug("cOrg_name="+projectCategoryStr);
logger.debug("year_from="+p_fromYear);
logger.debug("month_from="+p_fromMonth);
logger.debug("year_to="+p_toYear);
logger.debug("month_to="+p_toMonth);*/        
//logger.debug("projectCategoryStr.length() :"+projectCategoryStr.length());
                
		int i = 0;
		int j = 0;
		int m = Integer.parseInt(p_toYear);
		int n = Integer.parseInt(p_fromYear);
		int k= Integer.parseInt(p_toMonth);
		int l= Integer.parseInt(p_fromMonth);
		j=m-n;
		i=j*12+(k-l)+1;

		int[] mon = new int[i];
		int[] year = new int[i];
		mon[0] = l;
		year[0] = n;
		for(int a=1;a<i;a++)
		{
			mon[a]= mon[a-1]+1;
			if(mon[a-1]==12)
			{
				mon[a] = 1;
 				year[a] = year[a-1]+1;
			}
			else
				year[a] = year[a-1];
		}
		String[] Date = new String[i];
		for(int s=0;s<i;s++)
		{
			String mm = "";
			if(mon[s]<10)
			{
				mm = "0"+(new Integer(mon[s])).toString();
			}
			else
			{
				mm = (new Integer(mon[s])).toString();
			}
			Date[s] = (new Integer(year[s])).toString() + "/" + mm;
		}
		Vector year_month = new Vector();
		
		for(int count = 0;count < i;count++)
		{
			String chargesql = "Select a.grade grade,a.project_category_name category_name,sum(a.spend) hours ";
			chargesql += "From tss_v_project_report a,tss.tss_c_project_category b,tss.tss_c_project c ";
			chargesql += "Where a.project_category_name = b.project_category_name ";
			chargesql += "And a.project_name = c.project_name ";
			chargesql += "And b.company_code = '"+companyCodeStr+"' ";
			if(projectCategoryStr != null && projectCategoryStr.length() != 0)
			{
				chargesql += "And a.project_category_name = '"+projectCategoryStr+"' ";
			}
			chargesql += "And c.chargeable_ind = 'N' ";
			chargesql += "And to_char(a.spend_date,'YYYY/MM')='"+Date[count]+"' ";
//--------------- 2004-4-22 9:22 Update By Beyes -------------------//	
			if(count == 0)
			{
				chargesql += "And to_char(a.spend_date,'DD') >= '"+p_fromDay+"' ";
			}
			if(count == i-1)
			{
				chargesql += "And to_char(a.spend_date,'DD') <= '"+p_toDay+"' ";
			}
//-------------------------------------------------------------------//				
			chargesql += "Group By a.grade,a.project_category_name ";
			chargesql += "Order By a.grade,a.project_category_name";
//logger.debug("SQL is " + chargesql);
			//IISZ updated in 2013-07-11 begin
			Connection con = null;
			PreparedStatement chargestmt =null;
			//IISZ updated in 2013-07-11 end
			ResultSet chargers = null;
			ChargeReportContainer list = new ChargeReportContainer();
			
			try
			{
				// IISZ added in 2013-07-11 begin
				con = this.getConnection();
				chargestmt = con.prepareStatement(chargesql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				// IISZ added in 2013-07-11 end
				HashMap lHs_gradeTotal = new HashMap();
				HashMap lHs_categoryTotal = new HashMap();
				HashMap lHs_gradeCategoryTotal = new HashMap();
				float lf_total = 0f;
				HashMap lHm_Rate = new HashMap();
				ArrayList lAl_gradeNameList = new ArrayList();
				ArrayList lAl_categoryNameList = new ArrayList();

				chargers = chargestmt.executeQuery();
				while (chargers.next())
				{
					float lf_hours = 0f;
					String ls_grade = chargers.getString(1);
					String ls_category = chargers.getString(2);
					lf_hours = chargers.getFloat(3);
					if (!lAl_gradeNameList.contains(ls_grade))
						lAl_gradeNameList.add(ls_grade);
					if (!lAl_categoryNameList.contains(ls_category))
						lAl_categoryNameList.add(ls_category);
					              
					RateBean lRb_RateBean = RateProcess.createBean();
					lRb_RateBean.setGrade(ls_grade);
					RateBean[] lRb_RateBeanArr = RateProcess.find(con,lRb_RateBean);
																
								//Find a record
					if (lRb_RateBeanArr != null && lRb_RateBeanArr.length>0)
					{
						RateBean lRb_Bean = lRb_RateBeanArr[0];
						if (!lHm_Rate.containsKey(ls_grade))
						{
							lHm_Rate.put(ls_grade, new Float(lRb_Bean.getRate()));
						}
					}
					if(lHs_gradeTotal.containsKey(ls_grade))
					{
						Float gradehours = (Float)lHs_gradeTotal.get(ls_grade);
						Float gh = new Float(gradehours.floatValue()+lf_hours);
						gh = (Float)lHs_gradeTotal.put(ls_grade,gh);
					}
					else
					{
						Float gh = new Float(lf_hours);
						gh = (Float)lHs_gradeTotal.put(ls_grade,gh);
					}
					if (lHs_categoryTotal.containsKey(ls_category))
					{
						Float categoryhours = (Float)lHs_categoryTotal.get(ls_category);
						Float ch = new Float(categoryhours.floatValue()+lf_hours);
						ch = (Float)lHs_categoryTotal.put(ls_category,ch);
					}
					else
					{
						Float ch = new Float(lf_hours);
						ch = (Float)lHs_categoryTotal.put(ls_category,ch);
					}

					lf_total += lf_hours;
					Float gch = new Float(lf_hours);
					gch = (Float)lHs_gradeCategoryTotal.put(ls_grade + "~" + ls_category,gch);
				}
				
//logger.debug("---------------------- "+count+" --------------------------");
//logger.debug("Grade Total:"+lHs_gradeTotal);				
//logger.debug("Category Total:"+lHs_categoryTotal);
//logger.debug("Grade Category Total:"+lHs_gradeCategoryTotal);
//logger.debug("Total:"+lf_total);
//logger.debug("Rate:"+lHm_Rate);
//logger.debug("Grade Name List:"+lAl_gradeNameList);
//logger.debug("Category Name List:"+lAl_categoryNameList);
//logger.debug("---------------------- "+count+" --------------------------");
				
				if (!lHs_gradeTotal.isEmpty())
				{
//--------------- 2004-4-22 9:23 Update By Beyes -------------------//					
					String [] str_year_month = new String[3];
					str_year_month[0] = Date[count]; 
					if(count == 0)
					{
						str_year_month[1] = p_fromDay;
					}
					else
					{
						str_year_month[1] = "01";
					}
					if(count == i-1)
					{
						str_year_month[2] = p_toDay;
					}
					else
					{
						int yearInt = Integer.parseInt(Date[count].substring(0,4));
						int monthInt = Integer.parseInt(Date[count].substring(5,7));
						if (monthInt==4 || monthInt==6 || monthInt==9 || monthInt==11)
						{
							str_year_month[2] = "30";
						}							
						else if(monthInt == 2)
						{	
							if(yearInt%4==0 && (yearInt%100!=0 || yearInt%400==0))
								str_year_month[2] = "29";
							else
								str_year_month[2] = "28";
						}
						else
						{
							str_year_month[2] = "31";
						}					
					}
//-------------------------------------------------------------------//	
					year_month.addElement(str_year_month);
					
					list.setGradeTotal(lHs_gradeTotal);
					list.setCategoryTotal(lHs_categoryTotal);
					list.setGradeCategoryTotal(lHs_gradeCategoryTotal);
					list.setTotal(lf_total);
					list.setRate(lHm_Rate);
					list.setGradeNameList(lAl_gradeNameList);
					list.setCategoryNameList(lAl_categoryNameList);
                                                
					vec.addElement(list);
				}				
			}
			catch (Exception e)
			{
				logger.error(e,e);
			}finally{
				//IISZ updated in 2013-07-11 begin
				JdbcUtil.release(chargestmt, chargers);
				//IISZ updated in 2013-07-11 end
			}
		}
		if (vec.isEmpty())
		{
			message.setMessage("No record, please input another criteria.");
			return "tss/projectreport/ProjectReport.jsp";
		}
		else
		{
//logger.debug("vec.size():"+vec.size());		  
//logger.debug("year_month.size():"+year_month.size());
			request.setAttribute("year_month",year_month);
			request.setAttribute("vec",vec);
			return "tss/projectreport/CDIReport.jsp";
		}
	}
}
